package treballfinalUF2;
import java.sql.*;
public class Connexio  implements java.io.Serializable {

		private static final long serialVersionUID = 1L;		
		private Connection miConexion = null;

		public Connexio() {
		}

		public Connection getMiConexion() {
			return miConexion;
		}

		public void setMiConexion(Connection miConexion) {
			this.miConexion = miConexion;
		}

		public void creaBBDD() throws SQLException{
			
				Statement stmt = null;
				
				stmt = getMiConexion().createStatement();
				
		        stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS miBaseUF2FinalJSP;");
		        
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.empresa (idempresa MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "nomempresa varchar(300) NOT NULL,"
						+ "password varchar(300) NOT NULL,"
						+ "mail varchar(300) NOT NULL,"
						+ "descripcio varchar(300) NOT NULL,"
						+ "visible BOOLEAN NOT NULL,"
						+ "PRIMARY KEY (idempresa,mail));");
			try{
				stmt.executeUpdate("ALTER TABLE miBaseUF2FinalJSP.empresa ADD UNIQUE (mail)");
			}catch(SQLException e){
				System.err.println(e.getMessage());
			}
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.alumne (idalumne MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "nomalumne varchar(30) NOT NULL,"
						+ "password varchar(30) NOT NULL ,"
						+ "mail varchar(300) NOT NULL,"
						+ "grau varchar(30) NOT NULL,"
						+ "visible BOOLEAN NOT NULL,"
						+ "PRIMARY KEY (idalumne,mail));");
				try{
					stmt.executeUpdate("ALTER TABLE miBaseUF2FinalJSP.alumne ADD UNIQUE (mail)");
				}catch(SQLException e){
					System.err.println(e.getMessage());
				}
				
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.oferta (idoferta MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "idempresa MEDIUMINT NOT NULL,"
						+ "grau varchar(30)NOT NULL,"
						+ "titoloferta varchar(100)NOT NULL,"
						+ "descripcio varchar(300) NOT NULL,"
						+ "fechainici DATE NOT NULL ,"
						+ "fechafi DATE,"
						+ "PRIMARY KEY (idoferta));");
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.ofertaHistorica (idoferta MEDIUMINT NOT NULL ,"
						+ "idempresa MEDIUMINT NOT NULL,"
						+ "grau varchar(30)NOT NULL,"
						+ "titoloferta varchar(100)NOT NULL,"
						+ "descripcio varchar(300) NOT NULL,"
						+ "fechainici DATE NOT NULL ,"
						+ "fechafi DATE"
						+ ");");
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.ofertaempresaalumne"
						+ "(idoferta MEDIUMINT NOT NULL ,"
						+ "grau varchar(30) NOT NULL,"
						+ "idempresa MEDIUMINT NOT NULL,"
						+ "idalumne MEDIUMINT NOT NULL,"						
						+ "PRIMARY KEY (idoferta, idempresa, idalumne)) ; ");
				
				
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS miBaseUF2FinalJSP.ofertalumne"
						+ "(idofertaAlumne MEDIUMINT NOT NULL AUTO_INCREMENT,"
						+ "mail varchar(300) NOT NULL,"
						+ "titol varchar(100) NOT NULL,"
						+ "nomAlumne varchar(300) NOT NULL,"
						+ "descripcio varchar(600) NOT NULL,"						
						+ "PRIMARY KEY (idofertaAlumne, mail)"						
						+ ");");
				/*ALTER TABLE `ofertalumne` DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish_ci*/
				/*eliminació de les ofertes extintes*/
				java.util.Date date = new java.util.Date();
				java.text.SimpleDateFormat sdf=new java.text.SimpleDateFormat("yyyy/MM/dd");
				String fecha = sdf.format(date); 
				/*Esborrem la taula relacio ofertaempresaalumne*/
				stmt.executeUpdate("Delete  from miBaseUF2FinalJSP.ofertaempresaalumne" +
						" where idoferta in(select idoferta from miBaseUF2FinalJSP.oferta" +
						" where fechafi <= '"+ fecha + "') ;");
				/*Guardem en historic*/
				stmt.executeUpdate("Insert into  miBaseUF2FinalJSP.ofertaHistorica " +
						"SELECT * FROM miBaseUF2FinalJSP.oferta" +
						" where idoferta in(select idoferta from miBaseUF2FinalJSP.oferta" +
						" where fechafi <= '"+ fecha + "') ;");
				/*Esborrem la oferta */
				stmt.executeUpdate("Delete from miBaseUF2FinalJSP.oferta " +
						" where fechafi <= '"+ fecha + "' ;");
				//alumnes/admin/empresa demanada al exercici
				try{
				stmt.executeUpdate("INSERT INTO `miBaseUF2FinalJSP`.`alumne` " +
						"(`idalumne`, `nomalumne`, `password`, `mail`, `grau`, `visible`)" +
						" VALUES (NULL, 'alumne', 'alumne', 'alumne@gmail.com', 'DAW', '1');");
				}catch(SQLException e){
					System.err.println(e.getMessage());
				}
				try{
					stmt.executeUpdate("INSERT INTO `miBaseUF2FinalJSP`.`alumne` " +
							"(`idalumne`, `nomalumne`, `password`, `mail`, `grau`, `visible`)" +
							" VALUES (NULL, 'admin', '1234', 'admin@gmail.com', 'TOTS', '1');");
					}catch(SQLException e){
						System.err.println(e.getMessage());
					}
				try{
					stmt.executeUpdate("INSERT INTO `miBaseUF2FinalJSP`.`empresa`" +
							" (`idempresa`, `nomempresa`, `password`, `mail`, `descripcio`, `visible`)" +
							" VALUES (NULL, 'empresa', 'empresa', 'empresa@gmail.com', 'empresa de prova', '1');");
					
				}catch(SQLException e){
					System.err.println(e.getMessage());
				}
				
				
		}
		public ResultSet  retornaOfertaAlumne() throws SQLException{
            establishConnection();          
            String sql = "SELECT * FROM miBaseUF2FinalJSP.ofertalumne ";
            PreparedStatement ps =  getMiConexion().prepareStatement(sql);
            ps.executeQuery(sql);
            ResultSet result = ps.executeQuery(sql);        
           
            return result;
    }

		//Establiment de la connexio
		public Connection establishConnection (){		
			try {
				Class.forName("com.mysql.jdbc.Driver");

				String BaseDeDatos = "jdbc:mysql://localhost:3306/";

				setMiConexion(DriverManager.getConnection(BaseDeDatos, "root", "admin") );

				if(getMiConexion() != null){
					System.out.println("Conexion Exitosa!");
				}else{
					System.out.println("Conexion Fallida!");               
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

			return miConexion;
		}

		//Tanquem la connexio
		public void closeMiConnexion() {
			try {
				this.miConexion.close();
				System.out.println("Conexion cerrada con exito!");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
